﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace QuanLyBanHang_NewApp.DBAccess
{
    
    public class dbConnection
    {
        private SqlDataAdapter myAdapter;
        private SqlConnection conn;
        public dbConnection()
        {
            myAdapter = new SqlDataAdapter();
            conn = new SqlConnection(getConnectionString());
        }
        private SqlConnection openConnection()
        {
            if (conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
            {
                conn.Open();
            }
            return conn;
        }
        public static string getConnectionString()
        {
            return "Data Source=localhost;Initial Catalog=QUANLYBANHANG;Integrated Security=True";
      

        }
        public DataTable execSelectQuery(String _query, SqlParameter[] sqlParamater)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            dt = null;
            DataSet ds = new DataSet();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = _query;
                cmd.Parameters.AddRange(sqlParamater);
                cmd.ExecuteNonQuery();
                myAdapter.SelectCommand = cmd;
                myAdapter.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                
            }
            return dt;
        }
        public DataTable execSelectQueryNonParameter(String query)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            dt = null;
            DataSet ds = new DataSet();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
                myAdapter.SelectCommand = cmd;
                myAdapter.Fill(ds);
                dt = ds.Tables[0];

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                myAdapter.Dispose();
                cmd.Dispose();

            }
            return dt;
        }
        public DataTable execSelectProcedureQuery(String _query, SqlParameter[] Parameter)
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            dt = null;
            DataSet ds = new DataSet();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = _query;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(Parameter);
                cmd.ExecuteNonQuery();
                myAdapter.SelectCommand = cmd;
                myAdapter.Fill(ds);
                dt = ds.Tables[0];
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            return dt;
        }
        public DataTable execSelectProcedureQueryNonParameter(String _query)
        {
            SqlCommand myCommand = new SqlCommand();
            DataTable dataTable = new DataTable();
            dataTable = null;
            DataSet ds = new DataSet();
            try
            {
                myCommand.Connection = openConnection();
                myCommand.CommandText = _query;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.ExecuteNonQuery();
                myAdapter.SelectCommand = myCommand;
                myAdapter.Fill(ds);
                dataTable = ds.Tables[0];
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {

            }
            return dataTable;
        }
        public bool execInsertQuery(String _query, SqlParameter[] Parameter)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = _query;
                cmd.Parameters.AddRange(Parameter);
                myAdapter.InsertCommand = cmd;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            finally
            {
                myAdapter.Dispose();
                cmd.Dispose();
            }
            return true;
        }
        public bool execInsertProcedureQuery(String _query, SqlParameter[] Parameter)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = openConnection();
            cmd.CommandText = _query;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(Parameter);
            myAdapter.InsertCommand = cmd;
            cmd.ExecuteNonQuery();

            return true;
        }
        public bool execUpdateQuery(String _query, SqlParameter[] Parameter)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = _query;
                cmd.Parameters.AddRange(Parameter);
                myAdapter.UpdateCommand = cmd;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                myAdapter.Dispose();
                cmd.Dispose();
            }
            return true;
        }
        public bool execUpdateProcedureQuery(String _query, SqlParameter[] Parameter)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = _query;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(Parameter);
                myAdapter.UpdateCommand = cmd;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                myAdapter.Dispose();
                cmd.Dispose();
            }
            return true;
        }
        public bool execDeleteQuery(String _query, SqlParameter[] Parameter)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = _query;
                cmd.Parameters.AddRange(Parameter);
                myAdapter.DeleteCommand = cmd;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
            finally
            {
                myAdapter.Dispose();
                cmd.Dispose();
            }
            return true;
        }
        public bool execDeleteProcedureQuery(String _query, SqlParameter[] Parameter)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                cmd.Connection = openConnection();
                cmd.CommandText = _query;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(Parameter);
                myAdapter.DeleteCommand = cmd;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
            finally
            {
                myAdapter.Dispose();
                cmd.Dispose();
            }
            return true;
        }
    }
}
